-- @owner: zou_jialiang050
-- @date: 2023/4/12
-- @testpoint: 添加definer参数,用户无function权限;成功
--modified by: zhanghuan，2025.1.2，补充连接B库信息
@conn PrimaryDbBmode;
--step1:创建用户及schema并赋权;expect:成功
create user if not exists u_alter_004a password 'Test@1234';
create user if not exists u_alter_004b password 'Test@1234';
create user if not exists u_alter_004c sysadmin password 'Test@1234';
create schema if not exists sch_alter_004;
grant create,alter,drop,usage on schema sch_alter_004 to u_alter_004a, u_alter_004b, u_alter_004c;

--step2:使用普通用户创建function;expect:成功
set role u_alter_004a password 'Test@1234';
drop table if exists sch_alter_004.t_alter_004a;
create table sch_alter_004.t_alter_004a(c1 int);
insert into sch_alter_004.t_alter_004a values(100);
create or replace function sch_alter_004.f_alter_004a()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_004a;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_004.f_alter_004a sql security definer;

--step3:使用其他普通用户调用function;expect:成功
reset role;
revoke execute on all functions in schema sch_alter_004 from u_alter_004b;
set role u_alter_004b password 'Test@1234';
call sch_alter_004.f_alter_004a();

--step4:使用系统管理员用户创建function;expect:成功
reset role;
set role u_alter_004c password 'Test@1234';
drop table if exists sch_alter_004.t_alter_004c;
create table sch_alter_004.t_alter_004c(c1 int);
insert into sch_alter_004.t_alter_004c values(200);
create function sch_alter_004.f_alter_004c()
returns char(50)
as $$
declare
total int;
begin
select * into total from sch_alter_004.t_alter_004c;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_004.f_alter_004c sql security definer;

--step5:使用普通用户调用function;expect:成功
reset role;
set role u_alter_004b password 'Test@1234';
call sch_alter_004.f_alter_004c();

--step6:清理环境;expect:成功
reset role;
drop table if exists sch_alter_004.t_alter_004a;
drop table if exists sch_alter_004.t_alter_004c;
drop function if exists sch_alter_004.f_alter_004a;
drop function if exists sch_alter_004.f_alter_004c;
drop user if exists u_alter_004a cascade;
drop user if exists u_alter_004b cascade;
drop user if exists u_alter_004c cascade;
drop schema if exists sch_alter_004;

